# -*- coding: utf-8 -*-
"""
Created on Wed Jun 25 09:53:47 2025

@author: Administrator
"""
from docx import Document
import pandas as pd
from docx.shared import Pt
from docx.enum.text import WD_PARAGRAPH_ALIGNMENT
from docx.enum.table import WD_ALIGN_VERTICAL

# 读取 Excel 文件
df_fb = pd.read_excel(r'C:\Users\Administrator\Desktop\611022001004-龙驹寨街道办事处-凤麓\01发包方-.xls')
df_cb = pd.read_excel(r'C:\Users\Administrator\Desktop\611022001004-龙驹寨街道办事处-凤麓\02承包方-.xls')
df_dk = pd.read_excel(r'C:\Users\Administrator\Desktop\611022001004-龙驹寨街道办事处-凤麓\05地块信息-.xls')
df_jtcy = pd.read_excel(r'C:\Users\Administrator\Desktop\611022001004-龙驹寨街道办事处-凤麓\03家庭成员-.xls')

# 转换部分字段为字符串类型
df_fb['发包方代码'] = df_fb['发包方代码'].astype(str)
df_fb['负责人证件号码'] = df_fb['负责人证件号码'].astype(str)
df_fb['联系电话'] = df_fb['联系电话'].astype(str)

df_cb['承包方代码'] = df_cb['承包方代码'].astype(str)

# 增加计算一个发包方代码关联用
df_cb['发包方代码'] = df_cb['承包方代码'].str[:14]

df_dk['地块代码'] = df_dk['地块代码'].astype(str)
df_dk['承包方代码'] = df_dk['承包方代码'].astype(str)
df_dk['发包方代码'] = df_dk['发包方代码'].astype(str)

df_jtcy['承包方代码'] = df_jtcy['承包方代码'].astype(str)

# 经地块表进行简化，太复杂了
selected_columns = ["地块名称", "地块代码", "发包方代码", "承包方代码",
                    "地块东至", "地块西至", "地块南至", "地块北至", "实测面积",
                    "土地用途", "地力等级", "土地利用类型"]  # 替换为实际列名
new_df_dk = df_dk[selected_columns].copy()


# 填充单元格
def tichong(cell, nr):
    # ===========填写
    cell.text = ""
    # 添加段落和文本
    p = cell.paragraphs[0]
    run = p.add_run(nr)
    # 设置字体大小为小五号字（9磅）
    run.font.size = Pt(9)
    # 设置水平居中对齐
    p.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER
    cell.vertical_alignment = WD_ALIGN_VERTICAL.CENTER
    return


# 左对齐填充四至
def tichong1(cell, nr):
    # ===========填写
    cell.text = ""
    # 添加段落和文本
    p = cell.paragraphs[0]
    run = p.add_run(nr)
    # 设置字体大小为小五号字（9磅）
    run.font.size = Pt(9)
    # 设置水平居中对齐
    p.alignment = WD_PARAGRAPH_ALIGNMENT.LEFT
    cell.vertical_alignment = WD_ALIGN_VERTICAL.CENTER
    return


# 右对齐填充最后一页页码
def tichong2(cell, nr):
    # ===========填写
    cell.text = ""
    # 添加段落和文本
    p = cell.paragraphs[0]
    run = p.add_run(nr)
    # 设置字体大小为小五号字（9磅）
    run.font.size = Pt(9)
    # 设置水平居中对齐
    p.alignment = WD_PARAGRAPH_ALIGNMENT.RIGHT
    cell.vertical_alignment = WD_ALIGN_VERTICAL.CENTER
    return


# 清理多余空行
def clearkh(num_empty_lines):
    # num_empty_lines = 1
    # 找到最后一个表格的位置
    last_table_index = -1
    for i, element in enumerate(doc.element.body):
        if element.tag.endswith('tbl'):
            last_table_index = i
    if last_table_index == -1:
        print("文档中未找到表格")
    else:
        # 获取最后一个表格之前的所有段落
        paragraphs_before_last_table = []
        for i, p in enumerate(doc.paragraphs):
            if i < last_table_index:
                paragraphs_before_last_table.append(p)
            else:
                break
        # 从后往前删除指定数量的空段落
        empty_lines_deleted = 0
        for p in reversed(paragraphs_before_last_table):
            if empty_lines_deleted >= num_empty_lines:
                break
            if not p.text.strip():
                p._element.getparent().remove(p._element)
                p._element._parent = None
                empty_lines_deleted += 1
    return


# 遍历承包方表格所有行
for index, row in df_cb.iterrows():
    doc = Document(r'E:\20199\三调制图python代码\2025\李沛丹凤\44 - 副本.docx')
    cbf_daima = row['承包方代码']
    cbf_hetong = cbf_daima + "J"  # 合同
    cbf_xingm = row['承包方(代表)名称']
    cbf_tel = row['联系电话']
    cbf_zhjian = row['承包方(代表)证件号码']
    cbf_dizhi = row['承包方地址']
    fbf_daima = row['发包方代码']
    # 获取发包方的名称和负责人姓名
    fbf_name = df_fb.loc[df_fb["发包方代码"] == fbf_daima, "发包方名称"].iloc[0]
    fbf_fzr = df_fb.loc[df_fb["发包方代码"] == fbf_daima, "发包方负责人姓名"].iloc[0]

    # 获取地块子集
    field_name = "承包方代码"  # 替换为实际字段名
    # 筛选符合条件的记录并重排索引
    subset_df_dk = new_df_dk[new_df_dk[field_name] == cbf_daima].copy()
    subset_df_dk_reset = subset_df_dk.reset_index(drop=True)
    # 筛选符合条件的家庭成员记录并重排索引
    subset_df_jtcy = df_jtcy[df_jtcy[field_name] == cbf_daima].copy()
    subset_df_jtcy_reset = subset_df_jtcy.reset_index(drop=True)

    # 获取人员总数并填充
    no = len(subset_df_jtcy_reset)
    pe_no = "共 " + str(no) + " 人"
    table = doc.tables[5]
    tichong(table.cell(1, 7), pe_no)

    # 获取地块信息
    dk_info = []  # 初始化一个空列表，存放地块信息
    dk_geshu = len(subset_df_dk)   # 地块个数
    dk_mji = round(subset_df_dk["实测面积"].sum(), 2)
    dk_info0 = "共" + " " + str(dk_geshu) + " " + "块 " + str(dk_mji) + " 亩"

    # 填充doc表格第一页基本信息==========================
    table_count = len(doc.tables)
    table = doc.tables[0]
    # 填充发包方名称
    tichong(table.cell(1, 1), fbf_name)
    # 填充发包方负责人
    tichong(table.cell(1, 8), fbf_fzr)
    # 填充和合同编号
    tichong(table.cell(2, 1), cbf_hetong)
    # 填充承包方姓名
    tichong(table.cell(4, 1), cbf_xingm)
    # 填充承包方电话
    tichong(table.cell(4, 8), str(cbf_tel))
    # 填充承包方证件号码
    tichong(table.cell(5, 7), cbf_zhjian)
    # 填充承包方地址
    tichong(table.cell(6, 1), cbf_dizhi)
    # 填充承包方地块总数及面积
    tichong(table.cell(7, 1), dk_info0)

    # 填充最后一页的家庭成员
    for index, row in subset_df_jtcy_reset.iterrows():
        jtcy_info1 = row['成员姓名']
        jtcy_info2 = row['与户主关系']
        jtcy_info3 = row['成员证件号码']
        table = doc.tables[5]
        tichong(table.cell(index + 3, 1), jtcy_info1)
        tichong(table.cell(index + 3, 3), jtcy_info2)
        tichong(table.cell(index + 3, 4), jtcy_info3)

    # 循环地块子集数据框,填充地块信息
    for index, row in subset_df_dk_reset.iterrows():
        dk_info1 = row['地块名称'] + "\n" + "(" + row['地块代码'] + ")"
        dk_info2 = (
            '东:' + row['地块东至'] + "\n" +
            '南:' + row['地块南至'] + "\n" +
            '西:' + row['地块西至'] + "\n" +
            '北:' + row['地块北至']
            )
        dk_info3 = str(row['实测面积'])
        dk_info4 = str(row['实测面积'])
        dk_info5 = row['土地用途']
        dk_info6 = row['地力等级']
        dk_info7 = row['土地利用类型']
        if index <= 4:
            table = doc.tables[0]
            tichong(table.cell(index + 9, 0), dk_info1)
            tichong1(table.cell(index + 9, 1), dk_info2)
            tichong(table.cell(index + 9, 2), dk_info3)
            tichong(table.cell(index + 9, 4), dk_info4)
            tichong(table.cell(index + 9, 5), dk_info5)
            tichong(table.cell(index + 9, 7), dk_info6)
            tichong(table.cell(index + 9, 8), dk_info7)
        elif 5 <= index <= 12:
            table = doc.tables[1]
            tichong(table.cell(index - 3, 0), dk_info1)
            tichong1(table.cell(index - 3, 1), dk_info2)
            tichong(table.cell(index - 3, 2), dk_info3)
            tichong(table.cell(index - 3, 3), dk_info4)
            tichong(table.cell(index - 3, 4), dk_info5)
            tichong(table.cell(index - 3, 5), dk_info6)
            tichong(table.cell(index - 3, 6), dk_info7)
        elif 13 <= index <= 20:
            table = doc.tables[2]
            tichong(table.cell(index - 11, 0), dk_info1)
            tichong1(table.cell(index - 11, 1), dk_info2)
            tichong(table.cell(index - 11, 2), dk_info3)
            tichong(table.cell(index - 11, 3), dk_info4)
            tichong(table.cell(index - 11, 4), dk_info5)
            tichong(table.cell(index - 11, 5), dk_info6)
            tichong(table.cell(index - 11, 6), dk_info7)
        elif 21 <= index <= 28:
            table = doc.tables[3]
            tichong(table.cell(index - 19, 0), dk_info1)
            tichong1(table.cell(index - 19, 1), dk_info2)
            tichong(table.cell(index - 19, 2), dk_info3)
            tichong(table.cell(index - 19, 3), dk_info4)
            tichong(table.cell(index - 19, 4), dk_info5)
            tichong(table.cell(index - 19, 5), dk_info6)
            tichong(table.cell(index - 19, 6), dk_info7)
        elif 29 <= index <= 37:
            table = doc.tables[4]
            tichong(table.cell(index - 27, 0), dk_info1)
            tichong1(table.cell(index - 27, 1), dk_info2)
            tichong(table.cell(index - 27, 2), dk_info3)
            tichong(table.cell(index - 27, 3), dk_info4)
            tichong(table.cell(index - 27, 4), dk_info5)
            tichong(table.cell(index - 27, 5), dk_info6)
            tichong(table.cell(index - 27, 6), dk_info7)

    # 清理无用的地块表
    if dk_geshu <= 5:
        # 修改最后一个表格页码
        table00 = doc.tables[5]
        tichong2(table00.cell(0, 0), '第 2 页')
        # 清理多余空表格
        table1 = doc.tables[1]
        table2 = doc.tables[2]
        table3 = doc.tables[3]
        table4 = doc.tables[4]
        # 获取表格的父元素（通常是body）
        parent1 = table1._element.getparent()
        parent2 = table2._element.getparent()
        parent3 = table3._element.getparent()
        parent4 = table4._element.getparent()
        # 从父元素中移除表格
        parent1.remove(table1._element)
        parent2.remove(table2._element)
        parent3.remove(table3._element)
        parent4.remove(table4._element)
        # 清理多余空行
        clearkh(5)
    elif 5 < dk_geshu <= 13:
        # 修改最后一个表格页码
        table00 = doc.tables[5]
        tichong2(table00.cell(0, 0), '第 3 页')
        # 清理多余空表格
        table2 = doc.tables[2]
        table3 = doc.tables[3]
        table4 = doc.tables[4]
        # 获取表格的父元素（通常是body）
        parent2 = table2._element.getparent()
        parent3 = table3._element.getparent()
        parent4 = table4._element.getparent()
        # 从父元素中移除表格
        parent2.remove(table2._element)
        parent3.remove(table3._element)
        parent4.remove(table4._element)
        # 清理多余空行
        clearkh(4)
    elif 13 < dk_geshu <= 21:
        # 修改最后一个表格页码
        table00 = doc.tables[5]
        tichong2(table00.cell(0, 0), '第 4 页')
        # 清理多余空表格
        table3 = doc.tables[3]
        table4 = doc.tables[4]
        # 获取表格的父元素（通常是body）
        parent3 = table3._element.getparent()
        parent4 = table4._element.getparent()
        # 从父元素中移除表格
        parent3.remove(table3._element)
        parent4.remove(table4._element)
        # 清理多余空行
        clearkh(3)
    elif 21 < dk_geshu <= 29:
        # 修改最后一个表格页码
        table00 = doc.tables[5]
        tichong2(table00.cell(0, 0), '第 5 页')
        # 清理多余空表格
        table4 = doc.tables[4]
        # 获取表格的父元素（通常是body）
        parent4 = table4._element.getparent()
        # 从父元素中移除表格
        parent4.remove(table4._element)

    outfile = "C:/Users/Administrator/Desktop/611022001004-龙驹寨街道办事处-凤麓/1111/" + cbf_daima + "_" + cbf_xingm + ".docx"
    doc.save(outfile)







